-- EXEC sp_cancel_assm_release 21

ALTER PROC sp_cancel_assm_release( @assembly_release_id INT )
AS
BEGIN
	DECLARE @lot_id INT
	DECLARE @lot_number VARCHAR(50)

	DECLARE @bin_info VARCHAR(50)
	DECLARE @index INT
	DECLARE @bin_name VARCHAR(50)
	DECLARE @bin_column_name VARCHAR(50)
	DECLARE @bin_balance_column_name VARCHAR(50)

	DECLARE @queryString NVARCHAR(1000)
	DECLARE @ParamDefinition NVARCHAR(500)
	DECLARE @columnsString NVARCHAR(1000)

	DECLARE @wafer_cursor CURSOR
	DECLARE @BOM_LOT_CURSOR CURSOR
	DECLARE @wafer_id INT
	DECLARE @BOM_QTY_PULLED INT
	DECLARE @BOM_LOT_ID INT
	DECLARE @assm_pulled_qty INT
	DECLARE @LOT_STATUS VARCHAR(50)
	DECLARE @EXISTING_BIN_QTY INT
	DECLARE @EXISTING_RELEASE_QTY INT
	DECLARE @shippedLot_No VARCHAR(50)
	DECLARE @NEWLOT_ID  INT
	DECLARE @SHIPPEDLOT_ID INT
	/*
		select * from ASSM_RELEASE_TABLE
		select * from ASSM_RELEASE_LOT_TABLE
	*/
	DECLARE @LOTS_USED_FOR_ASSEMBLY_RELEASE_CURSOR CURSOR

	--Updatting Assembly Release Status to Cancelled.
	UPDATE Assm_Release_Table SET Status = 'CANCELLED' WHERE Assembly_Release_Id = @assembly_release_id
	
	SET @LOTS_USED_FOR_ASSEMBLY_RELEASE_CURSOR = CURSOR FOR 
	SELECT LOT_ID FROM FAB_LOTS_USED_FOR_ASSM_RELEASE WHERE ASSEMBLY_RELEASE_ID = @ASSEMBLY_RELEASE_ID

	OPEN @LOTS_USED_FOR_ASSEMBLY_RELEASE_CURSOR
	FETCH NEXT FROM @LOTS_USED_FOR_ASSEMBLY_RELEASE_CURSOR INTO @lot_id
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @columnsString = ''
	
		SET @wafer_cursor = CURSOR FOR SELECT SBRT.Wafer_Id, BIN_INFO FROM SORT_BIN_RELEASE_TABLE SBRT, ASSM_RELEASE_LOT_TABLE ARLT WHERE SBRT.LOT_ID = ARLT.LOT_ID
						AND ARLT.LOT_ID = @lot_id AND Assembly_Release_Id = @assembly_release_id and sbrt.wafer_id = arlt.wafer_id order by CAST(SBRT.wafer_ID AS INT)
		OPEN @wafer_cursor
		FETCH NEXT FROM @wafer_cursor INTO @wafer_id, @bin_info
		WHILE @@FETCh_STATUS = 0
		BEGIN

			SELECT @assm_pulled_qty = Qty_Pulled , @shippedLot_No = Shipped_Lot_Number FROM Assm_Release_Lot_Table WHERE Assembly_Release_Id = @assembly_release_id AND Lot_Id = @lot_id AND Wafer_Id = @wafer_id
			
			SELECT @SHIPPEDLOT_ID = SHIPPEDLot_Id ,  @NEWLOT_ID = SL.Lot_ID FROM SHIPPED_LOT SL, LOT_TABLE LT, PROCESS_STEP PS, PRODUCT P 
			WHERE LT.LOT_ID = SL.LOT_ID  AND SL.STATUS = 'Transit' AND PS.PROCESSSTEP_ID = LT.PROCESSSTEP_ID
			AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND PS.PS_TYPE = 'Die Bank' AND SL.SL_NEXT_PROCESS <> 'Die Bank'
			AND LT.LOT_NUMBER = @shippedLot_No

			IF @SHIPPEDLOT_ID > 0
			BEGIN
					DELETE FROM SHIPPED_LOT WHERE SHIPPEDLot_Id = @SHIPPEDLOT_ID
			END

			set @bin_info = substring( @bin_info, 4, len( @bin_info) )
			SET @columnsString = ''
			WHILE ( LEN(@bin_info) > 0 )
			BEGIN
				IF ( CHARINDEX ( '&', @bin_info, 1 ) > 0 ) 
				BEGIN
					SET @index = CHARINDEX ( '&', @bin_info, 1 )
					SET @bin_name = SUBSTRING( @bin_info, 0, @index ) 
					SET @bin_column_name = 'Bin_' + @bin_name
					SET @bin_balance_column_name = 'Bin_'+@bin_name+'_BALANCE_QTY'
					SET @columnsString = @columnsString + @bin_balance_column_name + ' = ' + @bin_column_name + ' , '
					SET @bin_info = SUBSTRING( @bin_info, @index+1, LEN( @bin_info ) )
				END	
				ELSE
				BEGIN
					SET @bin_name = @bin_info 
					SET @bin_column_name = 'Bin_'+@bin_name
					SET @bin_balance_column_name = 'Bin_'+@bin_name+'_BALANCE_QTY'
					--SET @columnsString = @columnsString + @bin_balance_column_name + ' = ' + @bin_column_name																-- COMMENTED
					SET @columnsString = @columnsString + @bin_balance_column_name + ' = ' + @bin_balance_column_name + '+' + CONVERT( VARCHAR, @assm_pulled_qty )			-- ADDED	
					SET @bin_info = ''
				END
			END
			SET @queryString = N'UPDATE SORT_BIN_RELEASE_TABLE SET '+@columnsString+', IS_NOTIFIED = 1 WHERE Lot_Id = @lot_id AND Wafer_Id = @wafer_id' 
			SET @ParamDefinition = N'@lot_id int,@wafer_id int'
			EXECUTE sp_executesql @queryString, @ParamDefinition, @lot_id = @lot_id, @wafer_id = @wafer_id
	
			SET @assm_pulled_qty = NULL

			FETCH NEXT FROM @wafer_cursor INTO @wafer_id, @bin_info
		END

		SELECT @LOT_STATUS = LOT_CURRENT_STATUS FROM LOT_TABLE WHERE LOT_ID = @lot_id
		IF @LOT_STATUS = 'Close'
		BEGIN
			UPDATE LOT_TABLE SET LOT_CURRENT_STATUS = 'Running' WHERE Lot_ID = @lot_id
		END
		SELECT @EXISTING_BIN_QTY = ISNULL(SUM(BIN_1_BALANCE_QTY), 0) FROM SORT_BIN_RELEASE_TABLE WHERE LOT_ID = @lot_id
		select @EXISTING_RELEASE_QTY = ISNULL(SUM(Qty_Pulled), 0) from assm_release_table art, assm_release_lot_table arlt where arlt.assembly_release_id = art.assembly_release_id 
		and arlt.lot_id = @lot_id AND STATUS NOT IN ('Closed', 'CANCELLED' ) And Lot_transferred = 1
		UPDATE LOT_TABLE SET QTY_IN = @EXISTING_BIN_QTY + @EXISTING_RELEASE_QTY WHERE LOT_ID = @lot_id

		CLOSE @wafer_cursor 
		DEALLOCATE @wafer_cursor
		FETCH NEXT FROM @LOTS_USED_FOR_ASSEMBLY_RELEASE_CURSOR INTO @lot_id	
	END
	
	
	--TO UPDATE THE BOM PART BALANCE QUANTITIES IN LOT_TABLE
	----
	DECLARE @IS_BOM_INV_PULLING_BY_WAFER_ID VARCHAR(100)
	SET @IS_BOM_INV_PULLING_BY_WAFER_ID = 'FALSE'
	SELECT @IS_BOM_INV_PULLING_BY_WAFER_ID = PROPERTY_VALUE FROM CONFIGURATION_TABLE WHERE PROPERTY_KEY = 'SCALAR.CHIPTRACK.OPERATIONS.RELEASE.ISBOMPULLINGBYWAFERID'
	IF ( @IS_BOM_INV_PULLING_BY_WAFER_ID = 'TRUE' )
	BEGIN
		--TO UPDATE THE BOM PART BALANCE QUANTITIES IN SORT_BIN_RELEASE_TABLE
		SET @WAFER_CURSOR = CURSOR FOR select sbrt.wafer_id, brlt.qty_pulled, BRLT.LOT_ID from sort_bin_release_table sbrt, bom_release_lot_table brlt where sbrt.lot_id = brlt.lot_id and sbrt.wafer_id = brlt.wafer_id and brlt.assembly_release_id = @assembly_release_id order by CAST(brlt.wafer_ID AS INT)
		OPEN @WAFER_CURSOR
		FETCH NEXT FROM @WAFER_CURSOR INTO @WAFER_ID, @BOM_QTY_PULLED, @BOM_LOT_ID
		WHILE @@FETCH_STATUS = 0
		BEGIN
			UPDATE SORT_BIN_RELEASE_TABLE SET BIN_1_BALANCE_QTY = ISNULL( BIN_1_BALANCE_QTY, 0 ) + @BOM_QTY_PULLED WHERE LOT_ID = @BOM_LOT_ID AND WAFER_ID = @WAFER_ID
			UPDATE LOT_TABLE SET LOT_CURRENT_STATUS = 'Running' WHERE LOT_ID = @BOM_LOT_ID
			FETCH NEXT FROM @WAFER_CURSOR INTO @WAFER_ID, @BOM_QTY_PULLED, @BOM_LOT_ID
		END
		CLOSE @WAFER_CURSOR
		DEALLOCATE @WAFER_CURSOR
	END
	ELSE
	BEGIN	
		SET @BOM_LOT_CURSOR = CURSOR FOR SELECT LOT_ID, QTY_PULLED FROM BOM_RELEASE_LOT_TABLE WHERE ASSEMBLY_RELEASE_ID = @ASSEMBLY_RELEASE_ID 	
		OPEN @BOM_LOT_CURSOR
		FETCH NEXT FROM @BOM_LOT_CURSOR INTO @BOM_LOT_ID, @BOM_QTY_PULLED
		WHILE @@FETCH_STATUS = 0
		BEGIN
	    
			IF @BOM_QTY_PULLED IS NOT NULL AND @BOM_QTY_PULLED > 0
			BEGIN
				UPDATE LOT_TABLE SET QTY_IN = ISNULL( QTY_IN, 0 ) + @BOM_QTY_PULLED WHERE LOT_ID = @BOM_LOT_ID
				UPDATE INVENTORY_LOT SET IL_QTY_IN = ISNULL( IL_QTY_IN, 0 ) + @BOM_QTY_PULLED, IL_QTY_BALANCE = ISNULL( IL_QTY_BALANCE, 0 ) + @BOM_QTY_PULLED WHERE LOT_ID = @BOM_LOT_ID

				IF( ( SELECT COUNT(*) FROM LOT_TABLE LT, INVENTORY_LOT IL WHERE LT.LOT_ID = IL.LOT_ID AND LT.LOT_ID = @BOM_LOT_ID AND IL_QTY_BALANCE <= 0 ) > 0 )
				BEGIN
    				UPDATE LOT_TABLE SET LOT_CURRENT_STATUS='Close' WHERE LOT_ID = @BOM_LOT_ID
				END
				ELSE
				BEGIN
					UPDATE LOT_TABLE SET LOT_CURRENT_STATUS='Running' WHERE LOT_ID = @BOM_LOT_ID
				END
			END		
			ELSE
			BEGIN
				UPDATE LOT_TABLE SET LOT_NUMBER = 'LOTNUMBER' WHERE LOT_NUMBER = 'LOTNUMBER'
			END

			FETCH NEXT FROM @BOM_LOT_CURSOR INTO @BOM_LOT_ID, @BOM_QTY_PULLED
		END
		CLOSE @BOM_LOT_CURSOR
		DEALLOCATE @BOM_LOT_CURSOR
	END
END